﻿Imports Microsoft.VisualBasic
Imports System.Data.SqlClient
Imports log4net

Public Class KhachSanServer
    Private Shared ReadOnly log As ILog = LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType)

    Public Shared Function CreateKhachSanFromReader(ByVal reader As SqlDataReader) As KhachSan
        Dim khachSan As New KhachSan()
        Try
            khachSan.ID = reader("ID_KS")
            If Not reader.IsDBNull(reader.GetOrdinal("NAME")) Then
                khachSan.NAME = DirectCast(reader("NAME"), String)
            End If
            If Not reader.IsDBNull(reader.GetOrdinal("ADDRESS_1")) Then
                khachSan.ADDRESS_1 = DirectCast(reader("ADDRESS_1"), String)
            End If
            If Not reader.IsDBNull(reader.GetOrdinal("IMG_URL")) Then
                khachSan.IMG_URL = DirectCast(reader("IMG_URL"), String)
            End If
            If Not reader.IsDBNull(reader.GetOrdinal("STARS")) Then
                khachSan.STARS = CInt(reader("STARS"))
            End If      
            If Not reader.IsDBNull(reader.GetOrdinal("HIEN_THI_GIA")) Then
                khachSan.HIENTHIGIA = reader("HIEN_THI_GIA")
            End If
            If Not reader.IsDBNull(reader.GetOrdinal("LINK")) Then
                khachSan.LINK = DirectCast(reader("LINK"), String)
            End If
            If Not reader.IsDBNull(reader.GetOrdinal("DESCRIPTION")) Then
                khachSan.DESCRIPTION = DirectCast(reader("DESCRIPTION"), String)
            End If
            If Not reader.IsDBNull(reader.GetOrdinal("QUY_DINH")) Then
                khachSan.QUY_DINH = DirectCast(reader("QUY_DINH"), String)
            End If
            If Not reader.IsDBNull(reader.GetOrdinal("IMG_LIST")) Then
                khachSan.IMG_LIST = DirectCast(reader("IMG_LIST"), String)
            End If
            If Not reader.IsDBNull(reader.GetOrdinal("VIDEO_LIST")) Then
                khachSan.VIDEO_LIST = DirectCast(reader("VIDEO_LIST"), String)
            End If
            khachSan.CITY_ID = reader("CITY_ID")

        Catch ex As Exception
        End Try
        Return khachSan
    End Function

    Public Shared Function CreateKhachSanDictionaryFromReader(ByVal reader As SqlDataReader) As Dictionary(Of String, Object)
        Dim dictObjectKhachSan As New Dictionary(Of String, Object)
        Try
            Util.SetDictionaryValueByKey(dictObjectKhachSan, "ID", (reader("ID")))
            If Not reader.IsDBNull(reader.GetOrdinal("NAME")) Then
                Util.SetDictionaryValueByKey(dictObjectKhachSan, "NAME", (reader("NAME")))
            End If
            If Not reader.IsDBNull(reader.GetOrdinal("ADDRESS_1")) Then
                Util.SetDictionaryValueByKey(dictObjectKhachSan, "ADDRESS_1", (reader("ADDRESS_1")))
            End If
            If Not reader.IsDBNull(reader.GetOrdinal("IMG_URL")) Then
                Util.SetDictionaryValueByKey(dictObjectKhachSan, "IMG_URL", (reader("IMG_URL")))
            End If
            If Not reader.IsDBNull(reader.GetOrdinal("STARS")) Then
                Util.SetDictionaryValueByKey(dictObjectKhachSan, "STARS", (reader("STARS")))
            End If
        Catch ex As Exception
        End Try
        Return dictObjectKhachSan
    End Function

    Public Shared Function GetListKhachSanByTinhThanh(ByVal id As Guid) As ArrayList
        Dim listKhachSan As New ArrayList
        Try
            Dim reader As SqlDataReader
            Dim sql As String = String.Format("SELECT * , ID as ID_KS FROM KHACH_SAN where DISPLAY='1' and DELETED='0' and city_id='{0}' order by NAME ", id)
            reader = Connection.ExcuteReader(sql)
            While (reader.Read())
                Dim khachSan As New Dictionary(Of String, Object)
                khachSan = CreateKhachSanDictionaryFromReader(reader)
                listKhachSan.Add(khachSan)
            End While
            reader.Close()
            Connection.Dispose()
        Catch ex As Exception
            Connection.CheckCloseConnect()
            ''''Throw ex
        End Try
        Return listKhachSan
    End Function

    Public Shared Function GetListKhachSanByIDTinhThanh(ByVal id As Guid, ByVal indexStar As Integer, Optional isPage As Boolean = True) As List(Of KhachSan)
        Dim listKhachSan As New List(Of KhachSan)
        Try
            Dim sql As String = ""
            Dim strNumberPgae As String = ""
            If isPage = True Then
                sql = "SELECT * FROM ( "
                strNumberPgae = String.Format(" ) a WHERE a.row > {0} and a.row <= {1} order by [ORDER] desc ", indexStar * Util.GetConfigKey("page_num"), (indexStar + 1) * Util.GetConfigKey("page_num"))
            End If
            Dim reader As SqlDataReader

            sql = String.Format("{0} SELECT * , ks.ID as ID_KS,ROW_NUMBER() OVER (ORDER BY STARS desc) as row, isNull((select top 1 SALE_PRICE FROM [GIA_PHONG] gp where IS_DISPLAY='1' and KHACH_SAN_ID=ks.ID", sql)
            sql = String.Format("{0} and LOAI_PHONG_ID  in (select top 1 ID FROM [LOAI_PHONG] lp where DISPLAY='1' and DELETED='0' and KHACH_SAN_ID=gp.KHACH_SAN_ID order by DIEN_TICH)),0) GIA ", sql)
            sql = String.Format("{0}, (select count(*)from DANH_GIA where DISPLAY='1' and DELETED='0' and KHACH_SAN_ID =ks.ID) COUNTDANHGIA ", sql)
            sql = String.Format("{0}  FROM KHACH_SAN ks where city_id='{1}' and DELETED='0' and DISPLAY='1'  {2} ", sql, id, strNumberPgae)
            'sql = String.Format("{0}  FROM KHACH_SAN ks where  ks.ID in (select KHACH_SAN_ID  from [KHACH_SAN_DIA_DANH] where  DIA_DANH_ID='{1}')  and DELETED='0' and DISPLAY='1' {2} ", sql, id, strNumberPgae)

            reader = Connection.ExcuteReader(sql)
            While (reader.Read())
                Dim khachSan As New KhachSan
                khachSan = CreateKhachSanFromReader(reader)
                khachSan.GIA = DirectCast(reader("GIA"), Double)
                If Not reader.IsDBNull(reader.GetOrdinal("COUNTDANHGIA")) Then
                    khachSan.COUNTDANHGIA = CInt(reader("COUNTDANHGIA"))
                End If
                listKhachSan.Add(khachSan)
            End While
            reader.Close()

            Connection.Dispose()
        Catch ex As Exception
            Connection.CheckCloseConnect()
            ''''Throw ex
        End Try
        Return listKhachSan
    End Function

    Public Shared Function GetCountKhachSanByIDTinhThanh(ByVal id As Guid) As Integer
        Dim num As Integer = 0
        Try
            Dim sql As String = ""

            Dim reader As SqlDataReader

            sql = String.Format("{0} SELECT count(*) as num ", sql)
            sql = String.Format("{0} FROM KHACH_SAN where city_id='{1}' and DELETED='0' and DISPLAY='1' ", sql, id)
            log.Info(sql)
            reader = Connection.ExcuteReader(sql)
            While (reader.Read())
                num = DirectCast(reader("num"), Integer)
            End While
            reader.Close()

            Connection.Dispose()
        Catch ex As Exception
            Connection.CheckCloseConnect()
            ''''Throw ex
        End Try
        Return num
    End Function

    Public Shared Function SearchNangCaoListKhachSanByIDTinhThanh(ByVal id As Guid, ByVal dangKM As Integer, ByVal arrStart As ArrayList, ByVal arrGia As ArrayList, ByVal optionSX As Integer, ByVal indexStar As Integer, ByVal nameSearch As String, Optional isPage As Boolean = True) As List(Of KhachSan)
        Dim listKhachSan As New List(Of KhachSan)
        Try
            Dim strNumberPgae As String = ""
            Dim sql As String = ""
            Dim oder_by As String = "ORDER BY STARS desc"
            If isPage = True Then
                sql = "SELECT * FROM ( "
                If optionSX = 0 Then ''5->1
                    sql = sql + "SELECT *, ROW_NUMBER() OVER (ORDER BY a.STARS desc) as row FROM ("
                Else
                    If optionSX = 1 Then
                        sql = sql + "SELECT *, ROW_NUMBER() OVER (ORDER BY a.GIAKM desc) as row FROM ("
                    End If
                    If optionSX = 2 Then
                        sql = sql + "SELECT *, ROW_NUMBER() OVER (ORDER BY a.GIA asc) as row FROM ("
                    End If
                    If optionSX = 3 Then
                        sql = sql + "SELECT *, ROW_NUMBER() OVER (ORDER BY a.GIA desc) as row FROM ("
                    End If
                    If optionSX = 4 Then ''a->z
                        sql = sql + "SELECT *, ROW_NUMBER() OVER (ORDER BY a.Name_KS ) as row FROM ("
                    End If
                    If optionSX = 5 Then ''z->a
                        sql = sql + "SELECT *, ROW_NUMBER() OVER (ORDER BY a.Name_KS desc) as row FROM ("
                    End If
                    If optionSX = 6 Then ''1->5
                        sql = sql + "SELECT *, ROW_NUMBER() OVER (ORDER BY a.STARS ) as row FROM ("
                    End If
                End If

                strNumberPgae = String.Format(" ) a ) b WHERE b.row > {0} and b.row <= {1}", indexStar * Util.GetConfigKey("page_num"), (indexStar + 1) * Util.GetConfigKey("page_num"))
            End If
            Dim reader As SqlDataReader

            sql = String.Format("{0} SELECT *, ks.ID as ID_KS, isNull(ks.NAME,'') Name_KS, ", sql)
            'sql = String.Format("{0} isNull((select top 1 GIA from KHUYEN_MAI where  KHACH_SAN_ID=ks.ID and NGAY_KET_THUC>= '{1}' ),0) GIAKM, ", sql, Now.Date)
            sql = String.Format("{0} isNull((select top 1 GIA from KHUYEN_MAI where  KHACH_SAN_ID=ks.ID and NGAY_BAT_DAU <= GETDATE() AND NGAY_KET_THUC >= GETDATE() and DELETED = '0' AND DISPLAY = '1'  ),0) GIAKM, ", sql, Now.Date)

            If arrGia.Count > 0 Then
                sql = String.Format("{0} isNull((select top 1 SALE_PRICE FROM [GIA_PHONG] gp where IS_DISPLAY='1' and KHACH_SAN_ID=ks.ID ", sql)
                sql = String.Format("{0} and (", sql)
                For j As Integer = 0 To arrGia.Count - 1
                    If j = arrGia.Count - 1 Then
                        sql = String.Format("{0}  (SALE_PRICE{1} SALE_PRICE)) ORDER BY TO_DATE desc  ),0) GIA  ", sql, arrGia(j))
                    Else
                        sql = String.Format("{0} (SALE_PRICE{1} SALE_PRICE) or ", sql, arrGia(j))
                    End If
                Next
            Else
                sql = String.Format("{0} isNull((select top 1 SALE_PRICE FROM [GIA_PHONG] gp where IS_DISPLAY='1' and KHACH_SAN_ID=ks.ID ", sql)
                sql = String.Format("{0} and LOAI_PHONG_ID  in (select top 1 ID FROM [LOAI_PHONG] lp where DISPLAY='1' and DELETED='0' and KHACH_SAN_ID=gp.KHACH_SAN_ID order by DIEN_TICH)),0) GIA ", sql)
            End If
            sql = String.Format("{0}, (select count(*)from DANH_GIA where DISPLAY='1' and DELETED='0' and KHACH_SAN_ID =ks.ID) COUNTDANHGIA ", sql)
            sql = String.Format("{0}  FROM KHACH_SAN ks where city_id='{1}' and DELETED='0' and DISPLAY='1' ", sql, id)
            sql = String.Format("{0} and ((ks.name like N'{1}%' or ks.name like N'%{1}' or ks.name like N'%{1}%') or ", sql, nameSearch.ToUpper())
            sql = String.Format("{0} (ks.name_kd like '{1}%' or ks.name_kd like '%{1}' or ks.name_kd like '%{1}%'))  ", sql, nameSearch.ToUpper())

            If arrStart.Count > 0 Then
                sql = String.Format("{0} and ( ", sql)
            End If
            For i As Integer = 0 To arrStart.Count - 1
                If i = arrStart.Count - 1 Then
                    sql = String.Format("{0}  ks.STARS={1})  ", sql, arrStart(i))
                Else
                    sql = String.Format("{0}  ks.STARS={1} or ", sql, arrStart(i))
                End If
            Next
            If arrGia.Count > 0 Then
                sql = String.Format("{0} and ( ks.ID in  (select KHACH_SAN_ID FROM [GIA_PHONG]  where  IS_DISPLAY='1' and DELETED='0' and ", sql)
            End If
            For j As Integer = 0 To arrGia.Count - 1
                If j = arrGia.Count - 1 Then
                    sql = String.Format("{0} (SALE_PRICE{1} SALE_PRICE)) ) ", sql, arrGia(j))
                Else
                    sql = String.Format("{0} (SALE_PRICE{1} SALE_PRICE) or ", sql, arrGia(j))
                End If
            Next
            If dangKM = 1 Then
                'sql = String.Format("{0} and ( ks.ID in (select KHACH_SAN_ID from KHUYEN_MAI where NGAY_KET_THUC>= '{1}'  ) ) ", sql, Now.Date)
                sql = String.Format("{0} and ( ks.ID in (select KHACH_SAN_ID from KHUYEN_MAI where NGAY_BAT_DAU <= GETDATE() AND NGAY_KET_THUC >= GETDATE() and DELETED = '0' AND DISPLAY = '1' ) ) ", sql)

                'sql = String.Format("{0} and ( ks.ID in (select c.KHACH_SAN_ID from KHUYEN_MAI AS c LEFT OUTER JOIN KHUYEN_MAI_LOAI_PHONG d ON c.ID = d.ID_KHUYEN_MAI AND d.DELETED = '0'   ", sql)
                'sql = String.Format("{0} LEFT OUTER JOIN LOAI_PHONG e ON d.ID_LOAI_PHONG = e.ID AND e.DELETED = '0' AND e.DISPLAY = '1', KHUYEN_MAI_DANHMUC h ", sql)
                'sql = String.Format("{0} WHERE  c.DELETED = '0' AND c.DISPLAY = '1' AND( (c.NGAY_BAT_DAU <= GETDATE() AND c.NGAY_KET_THUC >= GETDATE()) ", sql)
                'sql = String.Format("{0} OR c.NGAY_BAT_DAU >= GETDATE()) AND c.GIA IS NOT NULL ", sql)
                'sql = String.Format("{0} AND c.DANH_MUC_ID = h.ID AND h.DELETED = '0' AND h.DISPLAY = '1' ) ) ", sql)
            End If
            'sql = String.Format("{0} ", sql)
            'sql = String.Format("{0} ", sql)
            'sql = String.Format("{0} ", sql)
            'sql = String.Format("{0} ", sql)
            'sql = String.Format("{0} ", sql)
            'sql = String.Format("{0} ", sql)
            'sql = String.Format("{0} ", sql)
            '' sql = String.Format("{0} order by STARS desc ", sql)

            sql = String.Format(" {0} {1} ", sql, strNumberPgae)
            reader = Connection.ExcuteReader(sql)
            While (reader.Read())
                Dim khachSan As New KhachSan
                khachSan = CreateKhachSanFromReader(reader)
                khachSan.GIA = DirectCast(reader("GIA"), Double)
                If Not reader.IsDBNull(reader.GetOrdinal("COUNTDANHGIA")) Then
                    khachSan.COUNTDANHGIA = CInt(reader("COUNTDANHGIA"))
                End If
                listKhachSan.Add(khachSan)
            End While
            reader.Close()
            Connection.Dispose()
        Catch ex As Exception
            Connection.CheckCloseConnect()
            ''Throw ex
        End Try
        Return listKhachSan
    End Function

    Public Shared Function SearchNangCaoCountListKhachSanByIDTinhThanh(ByVal id As Guid, ByVal dangKM As Integer, ByVal arrStart As ArrayList, ByVal arrGia As ArrayList, ByVal optionSX As Integer, ByVal indexStar As Integer, ByVal nameSearch As String, Optional isPage As Boolean = True) As Integer
        Dim count As Integer = 0
        Try
            Dim strNumberPgae As String = ""
            Dim sql As String = ""
            Dim oder_by As String = "ORDER BY STARS desc"
            If isPage = True Then
                sql = "SELECT count(*) as num FROM ( "
                If optionSX = 0 Then
                    sql = sql + "SELECT *, ROW_NUMBER() OVER (ORDER BY a.STARS desc) as row FROM ("
                Else
                    If optionSX = 1 Then
                        sql = sql + "SELECT *, ROW_NUMBER() OVER (ORDER BY a.GIAKM desc) as row FROM ("
                    End If
                    If optionSX = 2 Then
                        sql = sql + "SELECT *, ROW_NUMBER() OVER (ORDER BY a.GIA asc) as row FROM ("
                    End If
                    If optionSX = 3 Then
                        sql = sql + "SELECT *, ROW_NUMBER() OVER (ORDER BY a.GIA desc) as row FROM ("
                    End If
                    If optionSX = 4 Then ''a->z
                        sql = sql + "SELECT *, ROW_NUMBER() OVER (ORDER BY a.Name_KS ) as row FROM ("
                    End If
                    If optionSX = 5 Then ''z->a
                        sql = sql + "SELECT *, ROW_NUMBER() OVER (ORDER BY a.Name_KS desc) as row FROM ("
                    End If
                    If optionSX = 6 Then ''1->5
                        sql = sql + "SELECT *, ROW_NUMBER() OVER (ORDER BY a.STARS ) as row FROM ("
                    End If
                End If

                strNumberPgae = String.Format(" ) a ) b")
            End If
            Dim reader As SqlDataReader

            sql = String.Format("{0} SELECT *,  isNull(ks.NAME,'') Name_KS, ", sql)
            'sql = String.Format("{0} isNull((select top 1 GIA from KHUYEN_MAI where  KHACH_SAN_ID=ks.ID and NGAY_KET_THUC>= '{1}' ),0) GIAKM, ", sql, Now.Date)
            sql = String.Format("{0} isNull((select top 1 GIA from KHUYEN_MAI where  KHACH_SAN_ID=ks.ID and NGAY_BAT_DAU <= GETDATE() AND NGAY_KET_THUC >= GETDATE() and DELETED = '0' AND DISPLAY = '1'  ),0) GIAKM, ", sql, Now.Date)

            If arrGia.Count > 0 Then
                sql = String.Format("{0} isNull((select top 1 SALE_PRICE FROM [GIA_PHONG] gp where IS_DISPLAY='1' and KHACH_SAN_ID=ks.ID ", sql)
                sql = String.Format("{0} and (", sql)
                For j As Integer = 0 To arrGia.Count - 1
                    If j = arrGia.Count - 1 Then
                        sql = String.Format("{0}  (SALE_PRICE{1} SALE_PRICE)) ORDER BY TO_DATE desc  ),0) GIA  ", sql, arrGia(j))
                    Else
                        sql = String.Format("{0} (SALE_PRICE{1} SALE_PRICE) or ", sql, arrGia(j))
                    End If
                Next
            Else
                sql = String.Format("{0} isNull((select top 1 SALE_PRICE FROM [GIA_PHONG] gp where IS_DISPLAY='1' and KHACH_SAN_ID=ks.ID ", sql)
                sql = String.Format("{0} and LOAI_PHONG_ID  in (select top 1 ID FROM [LOAI_PHONG] lp where DISPLAY='1' and DELETED='0' and KHACH_SAN_ID=gp.KHACH_SAN_ID order by DIEN_TICH)),0) GIA ", sql)
            End If
            sql = String.Format("{0}, (select count(*)from DANH_GIA where DISPLAY='1' and DELETED='0' and KHACH_SAN_ID =ks.ID) COUNTDANHGIA ", sql)
            sql = String.Format("{0}  FROM KHACH_SAN ks where city_id='{1}' and DELETED='0' and DISPLAY='1' ", sql, id)
            sql = String.Format("{0} and ((ks.name like N'{1}%' or ks.name like N'%{1}' or ks.name like N'%{1}%') or ", sql, nameSearch.ToUpper())
            sql = String.Format("{0} (ks.name_kd like '{1}%' or ks.name_kd like '%{1}' or ks.name_kd like '%{1}%'))  ", sql, nameSearch.ToUpper())

            If arrStart.Count > 0 Then
                sql = String.Format("{0} and ( ", sql)
            End If
            For i As Integer = 0 To arrStart.Count - 1
                If i = arrStart.Count - 1 Then
                    sql = String.Format("{0}  ks.STARS={1})  ", sql, arrStart(i))
                Else
                    sql = String.Format("{0}  ks.STARS={1} or ", sql, arrStart(i))
                End If
            Next
            If arrGia.Count > 0 Then
                sql = String.Format("{0} and ( ks.ID in  (select KHACH_SAN_ID FROM [GIA_PHONG]  where IS_DISPLAY='1' and ", sql)
            End If
            For j As Integer = 0 To arrGia.Count - 1
                If j = arrGia.Count - 1 Then
                    sql = String.Format("{0} (SALE_PRICE{1} SALE_PRICE)) ) ", sql, arrGia(j))
                Else
                    sql = String.Format("{0} (SALE_PRICE{1} SALE_PRICE) or ", sql, arrGia(j))
                End If
            Next
            If dangKM = 1 Then
                'sql = String.Format("{0} and ( ks.ID in (select KHACH_SAN_ID from KHUYEN_MAI where NGAY_KET_THUC>= '{1}'  ) ) ", sql, Now.Date)
                sql = String.Format("{0} and ( ks.ID in (select KHACH_SAN_ID from KHUYEN_MAI where NGAY_BAT_DAU <= GETDATE() AND NGAY_KET_THUC >= GETDATE() and DELETED = '0' AND DISPLAY = '1' ) ) ", sql)

                'sql = String.Format("{0} and ( ks.ID in (select c.KHACH_SAN_ID from KHUYEN_MAI AS c LEFT OUTER JOIN KHUYEN_MAI_LOAI_PHONG d ON c.ID = d.ID_KHUYEN_MAI AND d.DELETED = '0'   ", sql)
                'sql = String.Format("{0} LEFT OUTER JOIN LOAI_PHONG e ON d.ID_LOAI_PHONG = e.ID AND e.DELETED = '0' AND e.DISPLAY = '1', KHUYEN_MAI_DANHMUC h ", sql)
                'sql = String.Format("{0} WHERE  c.DELETED = '0' AND c.DISPLAY = '1' AND( (c.NGAY_BAT_DAU <= GETDATE() AND c.NGAY_KET_THUC >= GETDATE()) ", sql)
                'sql = String.Format("{0} OR c.NGAY_BAT_DAU >= GETDATE()) AND c.GIA IS NOT NULL ", sql)
                'sql = String.Format("{0} AND c.DANH_MUC_ID = h.ID AND h.DELETED = '0' AND h.DISPLAY = '1' ) ) ", sql)
            End If
            'sql = String.Format("{0} ", sql)
            'sql = String.Format("{0} ", sql)
            'sql = String.Format("{0} ", sql)
            'sql = String.Format("{0} ", sql)
            'sql = String.Format("{0} ", sql)
            'sql = String.Format("{0} ", sql)
            'sql = String.Format("{0} ", sql)
            '' sql = String.Format("{0} order by STARS desc ", sql)

            sql = String.Format(" {0} {1} ", sql, strNumberPgae)

            reader = Connection.ExcuteReader(sql)
            While (reader.Read())
                count = DirectCast(reader("num"), Integer)
            End While
            reader.Close()
            Connection.Dispose()
        Catch ex As Exception
            Connection.CheckCloseConnect()
            ''Throw ex
        End Try
        Return count
    End Function

    Public Shared Function GetKhachSanByIDKhachSan(ByVal id As Guid) As KhachSan
        Dim khachSan As New KhachSan
        Try
            Dim reader As SqlDataReader
            Dim sql As String = ""
            '', (SELECT NAME FROM TINH_THANH  where DISPLAY='1' and DELETED='0' and id=ks.CITY_ID) Name_City 
            sql = String.Format("{0} SELECT *, ks.ID as ID_KS ", sql)
            sql = String.Format("{0}  FROM KHACH_SAN ks  where ks.DISPLAY='1' and ks.DELETED='0' and ks.id='{1}' ", sql, id)
            log.Info(sql)
            reader = Connection.ExcuteReader(sql)
            While (reader.Read())
                khachSan = CreateKhachSanFromReader(reader)
            End While
            reader.Close()
            Connection.Dispose()
        Catch ex As Exception
            Connection.CheckCloseConnect()
            ''Throw ex
        End Try
        Return khachSan
    End Function

    Public Shared Function GetDichVuByIDKhachSan(ByVal id As Guid) As ArrayList
        Dim arrDichVu As New ArrayList
        Try
            Dim reader As SqlDataReader
            Dim sql As String = ""
            sql = String.Format("{0} SELECT isNull(b.NAME,'') AS NAME ", sql)
            sql = String.Format("{0} FROM KHACH_SAN a, DICH_VU b, KHACH_SAN_DICH_VU c WHERE a.ID = c.KHACH_SAN_ID AND b.ID = c.DICH_VU_ID AND a.DELETED = '0' AND b.DELETED = '0'  AND a.DISPLAY = '1' AND b.DISPLAY = '1' and a.ID ='{1}'  ", sql, id)
            log.Info(sql)
            reader = Connection.ExcuteReader(sql)
            While (reader.Read())
                arrDichVu.Add(reader("NAME"))
            End While
            reader.Close()
            Connection.Dispose()
        Catch ex As Exception
            Connection.CheckCloseConnect()
            ''Throw ex
        End Try
        Return arrDichVu
    End Function

    Public Shared Function GetCityNameByIDCity(ByVal id As Guid) As String
        Dim city As String = ""
        Try
            Dim reader As SqlDataReader
            Dim sql As String = ""
            sql = String.Format("{0} SELECT NAME ", sql)
            sql = String.Format("{0}  FROM TINH_THANH  where DISPLAY='1' and DELETED='0' and id='{1}' ", sql, id)
            log.Info(sql)
            reader = Connection.ExcuteReader(sql)
            While (reader.Read())
                city = reader("NAME")
            End While
            reader.Close()
            Connection.Dispose()
        Catch ex As Exception
            Connection.CheckCloseConnect()
            ''''Throw ex
        End Try
        Return city
    End Function

    Public Shared Function GetLoaiHotelByIdHotel(ByVal id As Guid) As List(Of KhachSan)
        Dim listKhachSan As New List(Of KhachSan)
        Try
            Dim sql As String = "SELECT *,ks.ID as ID_KS, isNull(lp.NAME,'') NAME_LP, isNull(lp.ID,'') ID_LP, isNull((select top 1 SALE_PRICE FROM [GIA_PHONG] gp where IS_DISPLAY='1' and KHACH_SAN_ID=ks.ID and LOAI_PHONG_ID =lp.ID ),0) GIA"
            sql &= " , lp.DIEN_TICH, lp.SO_NGUOI, lp.SO_GIUONG_DON, lp.SO_GIUONG_DOI, lp.DESCRIPTION as DESCRIPTIONLOAIPHONG "
            sql &= " FROM [LOAI_PHONG] lp, KHACH_SAN ks"
            sql &= " where ks.ID=lp.KHACH_SAN_ID "
            sql &= " and ks.DELETED='0' and ks.DISPLAY ='1' and lp.DISPLAY='1' and lp.DELETED='0' "
            sql = String.Format("{0} and ks.ID='{1}' order by GIA ", sql, id)
            Dim reader As SqlDataReader
            reader = Connection.ExcuteReader(sql)
            While (reader.Read())
                Dim khachSan As New KhachSan
                khachSan = CreateKhachSanFromReader(reader)
                khachSan.GIA = DirectCast(reader("GIA"), Double)
                khachSan.NAMELOAIPHONG = reader("NAME_LP")
                khachSan.IDLOAIPHONG = reader("ID_LP")
                khachSan.DIEN_TICH = reader("DIEN_TICH")
                khachSan.DESCRIPTIONLOAIPHONG = reader("DESCRIPTIONLOAIPHONG")
                khachSan.SO_NGUOI = reader("SO_NGUOI")
                khachSan.SO_GIUONG_DOI = reader("SO_GIUONG_DOI")
                khachSan.SO_GIUONG_DON = reader("SO_GIUONG_DON")
                listKhachSan.Add(khachSan)
            End While
            reader.Close()
            Connection.Dispose()

            If listKhachSan.Count = 0 Then
                Dim khachSan As New KhachSan
                listKhachSan.Add(khachSan)
            End If
        Catch ex As Exception
            Connection.CheckCloseConnect()
            ''''Throw ex
        End Try
        Return listKhachSan
    End Function

    Public Shared Function GetLoaiHotelByIdLoaiPhong(ByVal idKS As Guid, ByVal idLP As Guid) As KhachSan
        Dim khachSan As New KhachSan
        Try
            Dim sql As String = "SELECT *, ks.ID as ID_KS, isNull(lp.NAME,'') NAME_LP, isNull(lp.ID,'') ID_LP, isNull((select top 1 SALE_PRICE FROM [GIA_PHONG] gp where IS_DISPLAY='1' and KHACH_SAN_ID=ks.ID and LOAI_PHONG_ID =lp.ID ),0) GIA "
            sql &= " , lp.DIEN_TICH, lp.SO_NGUOI, lp.SO_GIUONG_DON, lp.SO_GIUONG_DOI, lp.DESCRIPTION as DESCRIPTIONLOAIPHONG "
            sql &= " ,isNull((select top 1 PRICE FROM [GIA_PHONG] gp where KHACH_SAN_ID=ks.ID and LOAI_PHONG_ID =lp.ID ),0) GIAKS  FROM [LOAI_PHONG] lp, KHACH_SAN ks"
            sql &= " where ks.ID=lp.KHACH_SAN_ID "
            sql &= " and ks.DELETED='0' and ks.DISPLAY ='1' and lp.DISPLAY='1' and lp.DELETED='0' "
            sql = String.Format("{0} and ks.ID='{1}' and lp.ID='{2}' ", sql, idKS, idLP)
            Dim reader As SqlDataReader
            reader = Connection.ExcuteReader(sql)
            While (reader.Read())
                khachSan = CreateKhachSanFromReader(reader)
                khachSan.GIA = DirectCast(reader("GIA"), Double)
                khachSan.NAMELOAIPHONG = reader("NAME_LP")
                khachSan.IDLOAIPHONG = reader("ID_LP")
                khachSan.GIAKS = reader("GIAKS")
                khachSan.DIEN_TICH = reader("DIEN_TICH")
                khachSan.DESCRIPTIONLOAIPHONG = reader("DESCRIPTIONLOAIPHONG")
                khachSan.SO_NGUOI = reader("SO_NGUOI")
                khachSan.SO_GIUONG_DOI = reader("SO_GIUONG_DOI")
                khachSan.SO_GIUONG_DON = reader("SO_GIUONG_DON")
            End While
            reader.Close()

            Connection.Dispose()
        Catch ex As Exception
            Connection.CheckCloseConnect()
            ''''Throw ex
        End Try
        Return khachSan
    End Function

    Public Shared Function GetDichVuTienNghiByIDLoaiPhong(ByVal idKS As Guid, ByVal idLP As Guid) As ArrayList
        Dim arrDichVuTienNghi As New ArrayList
        Try
            Dim reader As SqlDataReader
            Dim sql As String = "SELECT b.NAME AS NAME, b.IMG AS IMG"
            sql &= " FROM KHACH_SAN a, DICH_VU b, KHACH_SAN_DICH_VU c,LOAI_PHONG lp     "
            sql &= " WHERE lp.KHACH_SAN_ID =a.ID and "
            sql = String.Format("{0}  lp.ID='{1}' and a.ID = '{2}' ", sql, idLP, idKS)
            sql &= " AND a.ID = c.KHACH_SAN_ID AND b.ID = c.DICH_VU_ID"
            sql &= " AND a.DELETED = '0'AND b.DELETED = '0'"
            sql &= " AND a.DISPLAY = '1' AND b.DISPLAY = '1'"
            sql &= " UNION"
            sql &= " SELECT f.NAME,f.IMG FROM LOAI_PHONG_TIEN_NGHI e, TIEN_NGHI f  "
            sql = String.Format("{0}  WHERE e.LOAI_PHONG_ID = '{1}' ", sql, idLP)
            sql &= " AND f.ID = e.TIEN_NGHI_ID  AND f.DELETED = '0'"
            sql &= " AND f.DISPLAY ='1'GROUP BY f.NAME, f.IMG "

            reader = Connection.ExcuteReader(sql)
            While (reader.Read())
                arrDichVuTienNghi.Add(reader("NAME"))
            End While
            reader.Close()
            Connection.Dispose()
        Catch ex As Exception
            Connection.CheckCloseConnect()
            ''''Throw ex
        End Try
        Return arrDichVuTienNghi
    End Function

    Public Shared Function GetLoaiHotelByIdHotelAndIDLoai(ByVal id As Guid, ByVal idLoai As Guid) As KhachSan
        Dim khachSan As New KhachSan
        Try
            Dim sql As String = "SELECT *, ks.ID as ID_KS, isNull(lp.NAME,'') NAME_LP, isNull(lp.ID,'') ID_LP, isNull((select top 1 SALE_PRICE FROM [GIA_PHONG] gp where IS_DISPLAY='1' and KHACH_SAN_ID=ks.ID and LOAI_PHONG_ID =lp.ID ),0) GIA"
            sql &= " , lp.DIEN_TICH, lp.SO_NGUOI, lp.SO_GIUONG_DON, lp.SO_GIUONG_DOI, lp.DESCRIPTION as DESCRIPTIONLOAIPHONG "
            sql &= " FROM [LOAI_PHONG] lp, KHACH_SAN ks"
            sql &= " where ks.ID=lp.KHACH_SAN_ID "
            sql &= " and ks.DELETED='0' and ks.DISPLAY ='1' and lp.DISPLAY='1' and lp.DELETED='0' "
            sql = String.Format("{0} and ks.ID='{1}' and lp.ID='{2}'  order by GIA ", sql, id, idLoai)
            Dim reader As SqlDataReader
            reader = Connection.ExcuteReader(sql)
            While (reader.Read())
                ' khachSan = CreateKhachSanFromReader(reader)
                khachSan.ID = reader("ID_KS")
                khachSan.GIA = DirectCast(reader("GIA"), Double)
                khachSan.NAMELOAIPHONG = reader("NAME_LP")
                khachSan.IDLOAIPHONG = reader("ID_LP")
                khachSan.DIEN_TICH = reader("DIEN_TICH")
                khachSan.DESCRIPTIONLOAIPHONG = reader("DESCRIPTIONLOAIPHONG")
                khachSan.SO_NGUOI = reader("SO_NGUOI")
                khachSan.SO_GIUONG_DOI = reader("SO_GIUONG_DOI")
                khachSan.SO_GIUONG_DON = reader("SO_GIUONG_DON")

            End While
            reader.Close()

            Connection.Dispose()
        Catch ex As Exception
            Connection.CheckCloseConnect()
            ''''Throw ex
        End Try
        Return khachSan
    End Function
End Class
